﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using snat.proxy.items;

namespace snat.proxy.sqlapi {
    public static class MessageQuery {

        public static ListProxy<DBMessage> All() {
            return Query.SQLQuery<snat.proxy.items.DBMessage>(
                String.Join(" ", Query.SELECT, Query.ALL, Query.FROM, 
                                "(", Query.SELECT, Query.ALL, Query.FROM, Query.MessageProperties, 
                                        Query.NATURAL_JOIN, Query.MessageSend, ")",  
                                        Query.AS, "dummy", 
                                Query.NATURAL_JOIN, Query.Entity,
                                Query.ORDER_BY, Query.TimeStamp),
@"SELECT * FROM 
    ( SELECT  MessageID, TimeStamp FROM 
        MessageProperties NATURAL JOIN MessageSend NATURAL JOIN Entity ORDER BY TimeStamp, MessageID LIMIT ?position, ?bufferSize
    ) AS dummy 
NATURAL JOIN MessageReceive NATURAL JOIN Entity;
");
        }

        public static ListProxy<DBMessage> SentByBefore(List<int> ids, DateTime time) {
            StringBuilder idsList = new StringBuilder("(");
            foreach (int id in ids) {
                idsList.Append(id);
                idsList.Append(",");
            }
            idsList.Remove(idsList.Length - 1, 1);
            idsList.Append(")");


            return Query.SQLQuery<snat.proxy.items.DBMessage>(
                String.Join(" ", Query.SELECT, Query.ALL, Query.FROM, 
                                "(", Query.SELECT, Query.ALL, Query.FROM, Query.MessageProperties, 
                                        Query.NATURAL_JOIN, Query.MessageSend, "WHERE TimeStamp <", time.ToString("yyyyMMddHHmmss"), "AND EntityID in", idsList.ToString(), ")",  
                                        Query.AS, "dummy", 
                                Query.NATURAL_JOIN, Query.Entity,
                                Query.ORDER_BY, Query.TimeStamp),
"SELECT * FROM ( SELECT  MessageID, TimeStamp FROM  MessageProperties NATURAL JOIN MessageSend NATURAL JOIN Entity WHERE TimeStamp < " + time.ToString("yyyyMMddHHmmss") + " AND EntityID in "+idsList.ToString()+" ORDER BY TimeStamp, MessageID LIMIT ?position, ?bufferSize) AS dummy NATURAL JOIN MessageReceive NATURAL JOIN Entity;");
        }

        public static ListProxy<DBMessage> ReceivedByBefore(List<int> ids, DateTime time) {
            StringBuilder idsList = new StringBuilder("(");
            foreach (int id in ids) {
                idsList.Append(id);
                idsList.Append(",");
            }
            idsList.Remove(idsList.Length - 1, 1);
            idsList.Append(")");


            return Query.SQLQuery<snat.proxy.items.DBMessage>(
                String.Join(" ", Query.SELECT, Query.ALL, Query.FROM,
                                "(", Query.SELECT, Query.ALL, Query.FROM, Query.MessageProperties,
                                        Query.NATURAL_JOIN, Query.MessageReceive, "WHERE TimeStamp <", time.ToString("yyyyMMddHHmmss"), "AND EntityID in", idsList.ToString(), ")",
                                        Query.AS, "dummy",
                                Query.NATURAL_JOIN, Query.Entity,
                                Query.ORDER_BY, Query.TimeStamp),
"SELECT * FROM ( SELECT  MessageID, TimeStamp FROM  MessageProperties NATURAL JOIN MessageReceive NATURAL JOIN Entity WHERE TimeStamp < " + time.ToString("yyyyMMddHHmmss") + " AND EntityID in " + idsList.ToString() + " ORDER BY TimeStamp, MessageID LIMIT ?position, ?bufferSize) AS dummy NATURAL JOIN MessageSend NATURAL JOIN Entity;");
        }
    
        public static DBMessage LastMessage(bool isGreedy = true) {
            return Query.ConstantQuery<DBMessage>(String.Join(" ",
                        Query.SELECT, Query.ALL, Query.FROM,
                            "(", Query.SELECT, Query.ALL, Query.FROM, Query.MessageProperties,
                                    Query.NATURAL_JOIN, Query.MessageSend, ")",
                                    Query.AS, "dummy",
                            Query.NATURAL_JOIN, Query.Entity,
                            Query.ORDER_BY, Query.TimeStamp, Query.DESC, Query.LIMIT, "1", ",", "1"));
        }

    }
}
